﻿-- Create a schema for the version tables to avoid name collisions
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'XBSchemaVersion') 
BEGIN
	EXEC sp_executesql @statement = N'create schema [XBSchemaVersion] authorization [dbo]'
END
GO

IF ( OBJECT_ID(N'[XBSchemaVersion].[SchemaManagementVersion]', 'U') IS NULL )
BEGIN
	CREATE TABLE [XBSchemaVersion].[SchemaManagementVersion](
		[SchemaVersion] [varchar](50) NOT NULL	
	) ON [PRIMARY]
END
GO

-- This table tracks the schema change families.
IF ( OBJECT_ID(N'[XBSchemaVersion].[SchemaFamily]', 'U') IS NULL )
BEGIN
CREATE TABLE [XBSchemaVersion].[SchemaFamily](
	[SchemaFamilyId] [uniqueidentifier] NOT NULL,
	[Name] [varchar] (256) NOT NULL,
	[Description] [varchar](512) NULL,
	CONSTRAINT [PK_SchemaFamily] PRIMARY KEY CLUSTERED 
	(
		[SchemaFamilyId]
	)ON [PRIMARY]
) ON [PRIMARY]
END
GO

-- The schema install table tracks the installation information for a schema change.
IF ( OBJECT_ID(N'[XBSchemaVersion].[SchemaInstall]', 'U') IS NULL )
BEGIN
CREATE TABLE [XBSchemaVersion].[SchemaInstall](
	[SchemaInstallId] [uniqueidentifier] NOT NULL,
	[Name] [varchar] (256) NOT NULL,
	[Description] [varchar] (1024) NULL,
	[Status] [varchar] (50) NOT NULL,
	[StatusText] [varchar] (5000) NULL,
	[InstallDate] [datetime] NOT NULL,
	[InstalledBy] [varchar](256) NOT NULL,	
	CONSTRAINT [PK_XBSchemaVersion.SchemaInstall] PRIMARY KEY CLUSTERED 
	(
		[SchemaInstallId] 
	) ON [PRIMARY]
) ON [PRIMARY]
END
GO

-- Add DB PackageId to SchemaInstall table
IF NOT EXISTS (SELECT * FROM dbo.syscolumns WHERE id = OBJECT_ID(N'[XBSchemaVersion].[SchemaInstall]') AND name = 'PackageId' )
BEGIN 
    ALTER TABLE [XBSchemaVersion].[SchemaInstall] 
        ADD 
			PackageId uniqueidentifier NULL
END
GO

IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[XBSchemaVersion].[SchemaInstall]') AND name = 'PackageId' AND is_nullable = 1)
BEGIN 
	-- By default, set packageId to SchemaInstallId.
	UPDATE [XBSchemaVersion].[SchemaInstall] SET PackageId = SchemaInstallId WHERE PackageId IS NULL
	ALTER TABLE [XBSchemaVersion].[SchemaInstall]
		ALTER COLUMN PackageId uniqueidentifier NOT NULL
END
GO

-- Add InstallType to track if install is Update or cleanup
IF NOT EXISTS (SELECT * FROM dbo.syscolumns WHERE id = OBJECT_ID(N'[XBSchemaVersion].[SchemaInstall]') AND name = 'InstallType' )
BEGIN 
    ALTER TABLE [XBSchemaVersion].[SchemaInstall] 
        ADD 
			InstallType varchar(50) NULL
END
GO

IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[XBSchemaVersion].[SchemaInstall]') AND name = 'InstallType' AND is_nullable = 1)

BEGIN 
	UPDATE [XBSchemaVersion].[SchemaInstall] SET InstallType = 'Update' WHERE InstallType IS NULL
	ALTER TABLE [XBSchemaVersion].[SchemaInstall]
		ALTER COLUMN InstallType varchar(50) NOT NULL
END
GO


-- Import the data from the old database patches table to the new schema
IF ( OBJECT_ID(N'[dbo].[Database_Patches]', 'U') IS NOT NULL )
BEGIN
	-- Import the previous database patch information 
	insert into [XBSchemaVersion].SchemaInstall 
	(SchemaInstallId, packageId, [Name], Description, Status, StatusText, InstallDate, InstalledBy)
	select PatchId, PatchId, Convert(VARCHAR(50), PatchNumber), PatchDescription, 'Success', null, DateApplied, 'Unknown' 
	from Database_Patches where PatchId not in (select SchemaInstallId from XBSchemaVersion.SchemaInstall)
END
GO

-- This table stores the list of all schema changes applied to the database
IF ( OBJECT_ID(N'[XBSchemaVersion].[SchemaChangeLog]', 'U') IS NULL )
BEGIN
CREATE TABLE [XBSchemaVersion].[SchemaChangeLog](
	[SchemaChangeLogId] [uniqueidentifier] NOT NULL,
	[Description] [varchar](512) NULL,
	[SchemaInstallId] [uniqueidentifier] NOT NULL,
	[SchemaFamilyId] [uniqueidentifier] NOT NULL,
	CONSTRAINT [PK_SchemaChangeLog] PRIMARY KEY CLUSTERED 
	(
		[SchemaChangeLogId]
	) ON [PRIMARY]
) ON [PRIMARY]
END
GO

-- Foreign key to the family table which groups schema changes together into families. 
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[XBSchemaVersion].[FK_SchemaChangeLog_SchemaFamily]') AND parent_object_id = OBJECT_ID(N'[XBSchemaVersion].[SchemaChangeLog]'))
BEGIN
	ALTER TABLE [XBSchemaVersion].[SchemaChangeLog]  WITH CHECK ADD  CONSTRAINT [FK_SchemaChangeLog_SchemaFamily] FOREIGN KEY([SchemaFamilyId])
	REFERENCES [XBSchemaVersion].[SchemaFamily] ([SchemaFamilyId])
END
GO

-- Foreign key to the schema install table to show which install id this change was applied with
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[XBSchemaVersion].[FK_SchemaChangeLog_SchemaInstall]') AND parent_object_id = OBJECT_ID(N'[XBSchemaVersion].[SchemaChangeLog]'))
BEGIN
	ALTER TABLE [XBSchemaVersion].[SchemaChangeLog]  WITH CHECK ADD  CONSTRAINT [FK_SchemaChangeLog_SchemaInstall] FOREIGN KEY([SchemaInstallId])
	REFERENCES [XBSchemaVersion].[SchemaInstall] ([SchemaInstallId])
END
GO

-- Create a view for querying the currently applied schema changes
IF ( OBJECT_ID(N'[XBSchemaVersion].[view_SchemaChanges]', 'V') IS NOT NULL )
BEGIN
	drop view [XBSchemaVersion].[view_SchemaChanges]
end
GO

CREATE VIEW [XBSchemaVersion].[view_SchemaChanges]
AS
SELECT 
    CHGLOG.SchemaChangeLogId, 
    CHGLOG.Description AS SchemaChangeDescription, 
    INST.Status, 
    INST.StatusText,
    FAM.SchemaFamilyId, 
    FAM.Name AS FamilyName, 
    FAM.Description AS SchemaFamilyDescription, 
    INST.Name as PackageName, 
    INST.Description AS PackageDescription, 
    INST.InstallDate, 
    INST.InstalledBy,
    INST.SchemaInstallId AS InstallId
FROM XBSchemaVersion.SchemaInstall AS INST 
INNER JOIN XBSchemaVersion.SchemaChangeLog AS CHGLOG ON INST.SchemaInstallId = CHGLOG.SchemaInstallId 
INNER JOIN XBSchemaVersion.SchemaFamily AS FAM ON CHGLOG.SchemaFamilyId = FAM.SchemaFamilyId
GO

-- Create a view for querying the currently applied schema changes
IF ( OBJECT_ID(N'[XBSchemaVersion].[view_SchemaChangeHistory]', 'V') IS NOT NULL )
BEGIN
	drop view [XBSchemaVersion].[view_SchemaChangeHistory]
end
GO

CREATE VIEW [XBSchemaVersion].[view_SchemaChangeHistory]
AS
SELECT     
    COALESCE(CHGLOG.SchemaChangeLogId, INST.SchemaInstallId) AS SchemaChangeLogId,
    COALESCE(CHGLOG.Description, INST.Description) AS SchemaChangeDescription, 
    INST.Status, 
    INST.StatusText, 
    COALESCE(FAM.SchemaFamilyId, INST.SchemaInstallId) AS SchemaFamilyId,
    COALESCE(FAM.Name, INST.Name) AS FamilyName, 
    COALESCE(FAM.Description, INST.Description) AS SchemaFamilyDescription, 
    INST.Name AS PackageName, 
    INST.Description AS PackageDescription, 
    INST.InstallDate, 
    INST.InstalledBy, 
    INST.SchemaInstallId AS InstallId
FROM XBSchemaVersion.SchemaFamily AS FAM
INNER JOIN XBSchemaVersion.SchemaChangeLog AS CHGLOG ON FAM.SchemaFamilyId = CHGLOG.SchemaFamilyId 
RIGHT OUTER JOIN XBSchemaVersion.SchemaInstall AS INST ON INST.SchemaInstallId = CHGLOG.SchemaInstallId
GO
